Genesis Management Services Pty Ltd gmservices.com.au

Home Contact Search

genesis

SERVICES

Planning

Finance

Marketing

Personnel

EXPERIENCE

Business

Property

Tourism

Government

Education

UNIT 15 SPREAD-SHEET BUDGETING – PART 2

A	B	C	D	E	F	G	H
2	BASIC ENTERPRISES	- NOTES				NOTES	ONLY (DO NOT ENTER ON YOUR SPREADSHEET)
3						DOUBLE
4	PROFIT AND LOSS	JULY	AUG	SEPT	TOTAL	ENTRY	Notice how rows 41, 76 and 113 pick up these entries
5
6	INCOME	5 0000	60000	70000	=SUM(C6:E6)	C83
7	LESS VARIABLE COSTS
8	Material
9	Stock at beginning	0	=C11*-1	=D11*-1	=C9		July stock zero because business just starting
10	Purchases	28000	20000	30000	=SUM(C10:E10)	C94	Double entry is in row 94
11	Stock at end	-15000	-17000	-19000	=E11	C82	Stock figures from stock-take sheets
12	COST OF MATS USED	=C9+C10+C11	=D9+D10+D11	=E9+E10+E11	=SUM(C12:E12)		Total or Calculation
13	Direct Labour	2000	3000	4000	=SUM(C13:E13)	C49
14	Commission payable	1000	2000	3000	=SUM(C14:E14)	C50
15	TOTAL VARIABLE COSTS	=SUM(C12:C14)	=SUM(D12:D14)	=SUM(E12:E14)	=SUM(C15:E15)		Total or Calculation
16	CONTRIBUTION	=C6-C15	=D6-D15	=E6-E15	=SUM(C16:E16)		Total or Calculation
17	LESS OVERHEADS
18	Directors Salaries	4000	5000	4000	=SUM(C18:E18)	C54	Double entry in Cash Flow because cash paid is the same.
19	Advertising	1000	2000	1000	=SUM(C19:E19)	C80	Double entry in Balance Sheet because cash paid not the same.
20	Wages	9000	10000	9000	=SUM(C20:E20)	C100	Double entry in Balance Sheet because cash paid not the same.
21	Bad debts	1000	1000	1000	=SUM(C21:E21)	C83	Double entry in Balance Sheet because cash paid not the same.
22	Superannuation	1000	1000	1000	=SUM(C22:E22)	C96	Double entry in Balance Sheet because cash paid not the same.
23	Admin. Salaries	1000	1000	1000	=SUM(C23:E23)	C95	Double entry in Balance Sheet because cash paid not the same.
24	Telephone & Postage	1000	1000	1000	=SUM(C24:E24)	C97	Double entry in Balance Sheet because cash paid not the same.
25	Travel	1000	1000		=SUM(C25:E25)	C98	Double entry in Balance Sheet because cash paid not the same.
26	Printing & Stationery	1000	1000	1000	=SUM(C26:E26)	C59	Double entry in Cash Flow because cash paid is the same.
27	Legal & Accounting	1000	1000	1000	=SUM(C27:E27)	C60	Double entry in Cash Flow because cash paid is the same.
28	Sundries	1000	1000	1000	=SUM(C28:E28)	C61	Double entry in Cash Flow because cash paid is the same.
29	Rent	2000	2000	2000	=SUM(C29:E29)	C62	Double entry in Cash Flow because cash paid is the same.
30	Electricity & Water	1000	1000	1000	=SUM(C30:E30)	C99	Double entry in Balance Sheet because cash paid not the same.
31	Insurance	1000	1000	1000	=SUM(C31:E31)	C81	Double entry in Balance Sheet because cash paid not the same.
32	Depreciation - 10% pa	=+C87*0.1/12	=+D87*0.1/12	=+E87*0.1/12	=SUM(C32:E32)	C88	Figures won't show until Row 87 figures calculated (see B/S)
33	Lease Rentals	1000	1000	1000	=SUM(C33:E33)	C66	Double entry in Cash Flow because cash paid is the same.
34	Repairs	1000	1000	1000	=SUM(C34:E34)	C67	Double entry in Cash Flow because cash paid is the same.
35	General Expenses	1000	1000	1000	=SUM(C35:E35)	C68	Double entry in Cash Flow because cash paid is the same.
36	Interest on Loan @ 10% pa	=+C104*0.1/12	=+D104*0.1/12	=+E104*0.1/12	=SUM(C36:E36)	C71	Figures won't show until Row 104 figures calculated
37	Bank Charges	1000	1000	1000	=SUM(C37:E37)	C69	Double entry in Cash Flow because cash paid is the same.
38	TOTAL OVERHEADS	=SUM(C18:C37)	=SUM(D18:D37)	=SUM(E18:E37)	=SUM(C38:E38)		Total or Calculation
39	NET PROFIT	=C16-C38	=D16-D38	=E16-E38	=SUM(C39:E39)	C109	Total or Calculation
40

41	CASH FLOW	=+$C$4	=+$D$4	=+$E$4	=+$F$4		NOTES ONLY (DO NOT ENTER ON YOUR SPREADSHEET)
42
43	INFLOW
44	Capital	100000	0	0	=SUM(C44:E44)	C108
45	Bank Loan	36000	0	0	=SUM(C45:E45)	C104
46	Debtors			=C6	=SUM(C46:E46)
47	TOTAL INFLOW	=SUM(C44:C46)	=SUM(D44:D46)	=SUM(E44:E46)	=SUM(C47:E47)		Total or Calculation
48	OUTFLOW
49	Direct Labour	=+C13	=+D13	=+E13	=+F13		Assumes same amount in P & L is paid
50	Commission Payable	=C14	=D14	=E14	=SUM(C50:E50)		Assumes same amount in P & L is paid
51	Creditors / Materials		=C94	=D94	=SUM(C51:E51)
52	Advertising	5000			=SUM(C52:E52)	C80	Amount paid exceeds P&L and therefore creates a prepayment
53	Wages	8000	=+C20	=+D20	=SUM(C53:E53)	C100	Amount paid is less than P&L and therefore creates an accrual
54	Directors Salaries	=C18	=D18	=E18	=SUM(C54:E54)		Assumes same amount in P & L is paid
55	Superannuation	0	0	0	=SUM(C55:E55)		Amount paid is less than P&L and therefore creates an accrual
56	Admin. Salaries		=+C23	=+D23	=SUM(C56:E56)		Amount paid is less than P&L and therefore creates an accrual
57	Telephone & Postage		1000	1000	=SUM(C57:E57)		Amount paid is less than P&L and therefore creates an accrual
58	Travel	0	2000		=SUM(C58:E58)	C98	Amount paid is less than P&L and therefore creates an accrual
59	Printing & Stationery	=C26	=D26	=E26	=SUM(C59:E59)		Assumes same amount in P & L is paid
60	Legal & Accounting	=C27	=D27	=E27	=SUM(C60:E60)		Assumes same amount in P & L is paid
61	Sundries	=C28	=D28	=E28	=SUM(C61:E61)		Assumes same amount in P & L is paid
62	Rent	=C29	=D29	=E29	=SUM(C62:E62)		Assumes same amount in P & L is paid
63	Electricity & Water	0	700	700	=SUM(C63:E63)	C99	Amount paid is less than P&L and therefore creates an accrual
64	Insurance	12000	0	0	=SUM(C64:E64)	C81	Amount paid exceeds P&L and therefore creates a prepayment
65	Expenditure on Fixed Assets	18000	0	0	=SUM(C65:E65)	C87	Double entry picked up on row 87
66	Lease Rentals	=C33	=D33	=E33	=SUM(C66:E66)		Assumes same amount in P & L is paid
67	Repairs	=C34	=D34	=E34	=SUM(C67:E67)		Assumes same amount in P & L is paid
68	General Expenses	=+C35	=+D35	=+E35	=SUM(C68:E68)		Assumes same amount in P & L is paid
69	Bank Charges	=C37	=D37	=E37	=SUM(C69:E69)		Assumes same amount in P & L is paid
70	Loan Repayments - Principal	2000	2000	2000	=SUM(C70:E70)	C104	Assumes an 18 month period. Formula could be C45/18
71	Loan Repayments - Interest	=C36	=D36	=E36	=SUM(C71:E71)		Assumes same amount in P & L is paid
72
73	TOTAL OUTFLOW	=SUM(C49:C71)	=SUM(D49:D71)	=SUM(E49:E71)	=SUM(C73:E73)		Total or Calculation
74	NET CASH FLOW	=C47-C73	=D47-D73	=E47-E73	=SUM(C74:E74)		Total or Calculation
75	PROGRESSIVE	=C74	=C75+D74	=D75+E74	=E75	C79	Total or Calculation

76	BALANCE SHEET	=+$C$4	=+$D$4	=+$E$4	=+$F$4	NOTES ONLY (DO NOT ENTER ON YOUR SPREADSHEET)
77
78	CURRENT ASSETS
79	Cash	=C75	=D75	=E75		This completes the double entry for cash payments & receipts
80	Prepaid Advertising	=C52-C19	=D52-D19+C80	=E52-E19+D80		Because the amount paid exceeds the amount in the P&L
81	Prepaid Insurance	=-C31+C64	=-D31+D64+C81	=-E31+E64+D81		Because the amount paid exceeds the amount in the P&L
82	Stock	=C11*-1	=D11*-1	=E11*-1
83	Debtors	=C6-C21	=D6-D21+C83	=E6-E21+D83-E46		Debtors balance is reduced by the bad debt
84
85	TOTAL CURRENT ASSETS	=SUM(C79:C84)	=SUM(D79:D84)	=SUM(E79:E84)		Total or Calculation
86	FIXED ASSETS
87	At Cost	=SUM(C65:C65)	=C87	=D87		Completes the double entry for capital expenditure in the cash flow
88	Less Provision for Depreciation	=SUM(C32:C32)*-1	=SUM(D32:D32)*-1+C88	=SUM(E32:E32)*-1+D88		Completes the double entry for depreciation in the P&L
89
90	TOTAL FIXED ASSETS	=SUM(C87:C89)	=SUM(D87:D89)	=SUM(E87:E89)		Total or Calculation
91	TOTAL ASSETS	=C85+C90	=D85+D90	=E85+E90		Total or Calculation
92
93	CURRENT LIABILITIES
94	Creditors (for materials)	=C10	=D10+C94-D51	=E10+D94-E51
95	Accrued Admin. Salaries	=C23-C56	=D23-D56+C95	=E23-E56+D95		Because the amount paid is less than the amount in the P&L
96	Accrued Superannuation	=C22	=D22+C96	=E22+D96		Because the amount paid is less than the amount in the P&L
97	Accrued Postage	=C24-C57	=D24-D57+C97	=E24-E57+D97		Because the amount paid is less than the amount in the P&L
98	Accrued Travel	=C25-C58	=D25-D58+C98	=E25-E58+D98		Because the amount paid is less than the amount in the P&L
99	Accrued Electricity & Water	=C30-C63	=D30-D63+C99	=E30-E63+D99		Because the amount paid is less than the amount in the P&L
100	Accrued Wages	=C20-C53	=D20-D53+C100	=E20-E53+D100		Because the amount paid is less than the amount in the P&L
101
102	TOTAL CURR LIABS	=SUM(C94:C101)	=SUM(D94:D101)	=SUM(E94:E101)		Total or Calculation
103	LONG-TERM LIABS
104	Long Term Loan	=C45-C70	=D45-D70+C104	=E45-E70+D104
105
106	TOTAL L.T. LIABS	=SUM(C104:C105)	=SUM(D104:D105)	=SUM(E104:E105)		Total or Calculation
107	CAPITAL
108	CAPITAL AT BEGINNING	=C44	=C108	=D108
109	NET PROFIT	=C39	=D39+C109	=E39+D109		Total or Calculation
110	TOTAL CAPITAL	=C108+C109	=D108+D109	=E108+E109		Total or Calculation
111	TOTAL LIABILITIES	=C102+C106+C110	=D102+D106+D110	=E102+E106+E110		Total or Calculation
112	PROOF	=C91-C111	=D91-D111	=E91-E111		Total or Calculation

113	RATIOS	=+$C$4	=+$D$4	=+$E$4	=+$F$4		NOTES ONLY (DO NOT ENTER ON YOUR SPREADSHEET)
114
115	CURRENT ASSETS	=C85	=D85	=E85
116	CURRENT LIABILITIES	=C102	=D102	=E102
117	WORKING CAPITAL	=C115-C116	=D115-D116	=E115-E116			Total or Calculation
118
119	Current Ratio	=C115/C116	=D115/D116	=E115/E116			Total or Calculation (& format cells to 2 decimal places)
120
121	LONG-TERM LOANS	=C104	=D104	=E104
122	CAPITAL	=C110	=D110	=E110
123
124	Debt / Equity (Gearing)	=C121/C122	=D121/D122	=E121/E122			Total or Calculation (& format cells to 2 decimal places)
125
126	NET PROFIT (A/TAX)-CUM	=C39	=D109	=E109
127	CAPITAL	=C110	=D110	=E110
128	R.O.I -Pa (Cumulative)	*=C12612/+C127**	*=D1266/+D127**	*=E1264/+E127**		Total or Calculation (& format cells to %)

and now for a more advanced exercise “Jones Travel”

this exercise is an expansion of the Jones Travel case study introduced earlier in this manual. The new features compared to Basic Enterprises are

a revised structure and formulae

a new section for a funds statement

a new front end for variables relating to sales growth rates, commission rates, sales quantities and prices, variable costs and overhead escalation rates. Other variables for interest rate , term of loan, depreciation period and rate could also have been included in this “front-end”

we start with some history “Jones Travel- Recent History”

you are not required to set this history up on a spread sheet

the main purposes for requiring you to study this history are

to realise where we will drawing our spreadsheet assumptions and figures from

to help you develop your financial analysis appreciation.

	B	C	D	E	F	G	H	I
2	JONES TRAVEL - RECENT HISTORY
3	PROFIT AND LOSS STATEMENT
4		Y/E 30/6/95	Y/E 30/6/96
5		12 months	JULY	AUGUST	2 MONTHS	2 MONTHS	2 MONTHS
6		Actual pa	Budget	Budget	JUL / AUG	JUL / AUG	JUL / AUG
7	GROSS SALES VOLUME				Budget	Actual	Variation
8	Air	950,000	80,000	100,000	180,000	150,000	30,000
9	Rail	450,000	40,000	60,000	100,000	110,000	(10,000)
10	Sea	33,000	5,000	8,000	13,000	10,000	3,000
11	Road	10,000	1,000	5,000	6,000	6,500	(500)
12	Accommodation	5,000	1,000	5,000	6,000	5,500	500
13	Tours	47,000	5,000	15,000	20,000	25,000	(5,000)
14	Souvenirs	15,220	5,000	10,000	15,000	12,500	2,500
15	TOTAL GROSS SALES	1,510,220	137,000	203,000	340,000	319,500	20,500
16	GROSS PROFIT						-
17	1. COMMISSIONS						-
18	Air	69,350	6,000	7,500	13,500	11,500	2,000
19	% Comm. to sales	7.3%	7.5%	7.5%	7.5%	7.7%	-0.2%
20	Rail	67,500	6,000	10,200	16,200	16,000	200
21	% Comm. to sales	15.0%	15.0%	17.0%	16.2%	14.5%	1.7%
22	Sea	5,610	850	1,200	2,050	1,450	600
23	% Comm. to sales	17.0%	17.0%	15.0%	15.8%	14.5%	1.3%
24	Road	1,250	125	625	750	800	(50)
25	% Comm. to sales	12.5%	12.5%	12.5%	12.5%	12.3%	0.2%
26	Accommodation	675	135	675	810	700	110
27	% Comm. to sales	13.5%	13.5%	13.5%	13.5%	12.7%	0.8%
28	Tours	8,695	1,000	3,000	4,000	4,500	(500)
29	% Comm. to sales	18.5%	20.0%	20.0%	20.0%	18.0%	2.0%
30							-
31	TOTAL COMMISSIONS	153,080	14,110	23,200	37,310	34,950	2,360
32	Average Commission %	10.2%	10%	11%	11.5%	11.4%	0.1%
33							-
34	2. SALE OF SOUVENIRS	15,220	5,000	12,000	17,000	23,000	(6,000)
35	LESS VARIABLE COSTS						-
36	MATERIAL						-
37	Stock at beginning	-	-	1,000	-	-	-
38	Purchases	4,500	2,500	4,000	6,500	8,500	(2,000)
39	Stock at End	-	(1,000)	(1,000)	(1,000)	(1,500)	500
40	COST- MATERIALS USED	4,500	1,500	4,000	5,500	7,000	(1,500)
41	Direct Labour	500	100	200	300	200	100
42	Commission Paid	200	50	100	150	200	(50)
43	TOTAL VARIABLE COSTS	5,200	1,650	4,300	5,950	7,400	(1,450)
44	CONTRIBUTION	10,020	3,350	7,700	11,050	15,600	(4,550)
45	Contribution %	66%	67%	64%	65%	68%	-2.8%
46	TOTAL GROSS PROFIT	163,100	17,460	30,900	48,360	50,550	(2,190)
47
48	(continued)	Y/E 30/6/95	Y/E 30/6/96
49		12 months	JULY	AUGUST	2 MONTHS	2 MONTHS	2 MONTHS
50		Actual pa	Budget	Budget	JUL / AUG	JUL / AUG	JUL / AUG
51	LESS OVERHEADS				Budget	Actual	Variation
52	Administrative Salaries		500	550	1,050	1,200	(150)
53	Advertising		1,000	1,000	2,000	2,300	(300)
54	Bank Charges	1,563	150	150	300	320	(20)
55	Deprec.- Furniture & Fittings	555	46	46	92	92	-
56	Deprec.- Office Equipment	5,000	417	417	834	834	-
57	Directors Salaries		100	100	200	200	-
58	Electricity & Water	560	50	50	100	120	(20)
59	General Expenses	1,563	200	200	400	350	50
60	Insurance	960	90	90	180	190	(10)
61	Interest on Bank Loan-10% pa		83	83	166	166	(0)
62	Leasing	4,300	500	500	1,000	1,000	-
63	Legal & Accounting	2,000	200	200	400	500	(100)
64	Licence Fees	1,500	150	150	300	300	-
65	Motor Vehicle Running Costs	4,200	400	400	800	750	50
66	Printing & Stationery	2,350	300	300	600	750	(150)
67	Rent	25,000	2,100	2,100	4,200	4,200	-
68	Repairs & Maintenance	1,632	150	150	300	320	(20)
69	Salaries	75,200	7,200	7,200	14,400	16,500	(2,100)
70	Staff Amenities	893	100	100	200	300	(100)
71	Subscriptions	1,650	150	150	300	300	-
72	Sundries		100	100	200	350	(150)
73	Telephone & Postage	3,200	400	400	800	700	100
74	Travel	523	200	200	400	850	(450)
75	TOTAL OVERHEADS	132,649	14,586	14,636	29,222	32,592	(3,370)
76	NET PROFIT (B/TAX)	30,451	2,874	16,264	19,138	17,958	1,180
77	Taxation	-			-		-
78	NET PROFIT (A/TAX)	30,451	2,874	16,264	19,138	17,958	1,180
79	Dividend	-			-		-
80	NET PROFIT RETAINED	30,451	2,874	16,264	19,138	17,958	1,180
81	CASH FLOW	Y/E 30/6/95	Y/E 30/6/96		Budget	Actual	Variation
82		12 months	JULY	AUGUST	2 MONTHS	2 MONTHS	2 MONTHS
83	INFLOW	Actual pa	Budget	Budget	JUL / AUG	JUL / AUG	JUL / AUG
84	Capital	30,000	30,000		30,000	30,000	-
85	Bank Loan	10,000		-	-		-
86	Receipts from Clients	24,023	20,000		20,000		20,000
87	Cash Sales (Commissions)	153,080	14,110	23,200	37,310	34,950	2,360
88	Debtors (Souvenirs)	9,539	5,681	5,000	10,681	9,430	1,251
89	TOTAL INFLOW	226,642	69,791	28,200	97,991	74,380	23,611
90	OUTFLOW						-
91	1. VARIABLE COSTS						-
92	Purchases (Creditors)	4,000	500	2,500	3,000	500	2,500
93	Direct Labour	500	100	200	300	200	100
94	Commission Paid	200	50	100	150	200	(50)
95	2. OVERHEADS				-		-
96	Administrative Salaries	0	500	550	1,050	1,200	(150)
97	Advertising	0	4,800		4,800	4,800	-
98	Bank Charges	1563	150	150	300	320	(20)
99	Directors Salaries	-	100	100	200	200	-
100	Electricity & Water	560	-	100	100	100	-
101	General Expenses	1,563	200	200	400	350	50
102	Insurance	960	1,080		1,080	1,080	-
103	Interest on Bank Loan-10% pa	-	83	83	166	166	-
104	Leasing	4,300	500	500	1,000	1,000	-
105	Legal & Accounting	2,000	200	200	400	500	(100)
106	Licence Fees	1,500	150	150	300	300	-
107	Motor Vehicle Running Costs	4,200	400	400	800	750	50
108	Printing & Stationery	2,350	300	300	600	750	(150)
109	Rent	25,000	6,300		6,300	6,300	-
110	Repairs & Maintenance	1,632	150	150	300	320	(20)
111	Salaries	75,200	5,400	9,000	14,400	14,400	-
112	Staff Amenities	893	100	100	200	300	(100)
113	Subscriptions	1,650	150	150	300	300	-
114	Sundries	-	100	100	200	350	(150)
115	Telephone & Postage	3,200		600	600	700	(100)
116	Travel	523	200	200	400	850	(450)
117	3. ASSETS				-
118	Petty Cash Advance	100			-	-	-
119	Clients Trust Account	24,023	20,000	-	20,000	20,000	-
120	Furniture & Fittings	7,555			-	-	-
121	Office Equipment	29,000			-	-	-
122	Land & Buildings	31,774			-	-	-
123	4. LIABILITIES						-
124	Income Taxation					-	-
125	Dividend					-	-
126	Loan Principal (monthly, 10 years)		83	83	167	167	-
127	Return Capital						-
128	TOTAL OUTFLOW	224,246	41,597	15,916	57,513	56,103	1,410
129	NET CASH FLOW	2,396	28,194	12,284	40,478	18,277	22,201
130	PROGRESSIVE BALANCE	2,396	30,590	42,874	42,874	20,673	22,201
131
132	BALANCE SHEET
133		Y/E 30/6/95	Y/E 30/6/96
134		12 months	JULY	AUGUST	2 MONTHS	2 MONTHS	2 MONTHS	2 MONTHS
135	CURRENT ASSETS	Actual pa	Budget	Budget	JUL / AUG	JUL / AUG	JUL / AUG	JUN / AUG
136					Budget	Actual	Variation	CHANGE
137	Cash at Bank	2,396	30,590	42,874		20,673	(22,201)	18,277
138	Client Trust Account	24,023	44,023	44,023		44,023	-	20,000
139	Petty Cash Advance	100	100	100		100	-	-
140	Prepayments - Insurance		990	900		890	(10)	890
141	Prepayments - Rent		4,200	2,100		2,100	-	2,100
142	Prepayments - Advertising		3,800	2,800		2,500	(300)	2,500
143	Stock (Souvenirs)	-	1,000	1,000		1,500	500	1,500
144	Debtors	5,681	5,000	12,000		19,251	7,251	13,570
145
146	TOTAL CURRENT ASSETS	32,200	89,703	105,797		91,037	(14,760)	58,837
147
148	FIXED ASSETS
149	Furniture & Fittings	7,555	7,555	7,555		7,555	-	-
150	Less Provision for Deprec.	(555)	(601)	(647)		(647)	-	(92)
151	Office Equipment	29,000	29,000	29,000		29,000	-	-
152	Less Provision for Deprec.	(5,000)	(5,417)	(5,834)		(5,834)	-	(834)
153	Land & Buildings	31,774	31,774	31,774		31,774	-	-
154	TOTAL FIXED ASSETS	62,774	62,311	61,848		61,848	-	(926)
155
156	TOTAL ASSETS	94,974	152,014	167,645		152,885	(14,760)	57,911
157
158	CURRENT LIABILITIES
159	Creditors (Materials)	500	2,500	4,000		8,500	4,500	8,000
160	Client Ledger	24,023	44,023	44,023		24,023	(20,000)	-
161	Provision for Income Tax						-
162	Provision for Dividend						-
163	Accruals - Electricity & Water		50	-		20	20	20
164	Accruals - Telephone		400	200		-	(200)	-
165	Accruals - Salaries		1,800	-		2,100	2,100	2,100
166
167	TOTAL CURR LIABS	24,523	48,773	48,223		34,643	(13,580)	10,120
168
169	LONG-TERM LIABS
170	Long-term Loan	10,000	9,917	9,833		9,833	0	(167)
171
172	TOTAL L.T. LIABS	10,000	9,917	9,833		9,833	0	(167)
173
174	CAPITAL
175	Capital at Beginning	30,000	60,000	60,000		60,000	-	30,000
176	Net Profit Retained	30,451	33,325	49,589		48,409	(1,180)	17,958
177	TOTAL CAPITAL	60,451	93,325	109,589		108,409	(1,180)	47,958
178
179	TOTAL LIABILITIES	94,974	152,014	167,645		152,885	(14,760)	57,911
180	Proof	-	0	-		0	(0)	0
181
182	RATIOS	Y/E 30/6/95	Y/E 30/6/96
183		12 months	JULY	AUGUST	2 MONTHS	2 MONTHS	2 MONTHS	2 MONTHS
184		Actual pa	Budget	Budget	JUL / AUG	JUL / AUG	JUL / AUG	JUN / AUG
185					Budget	Actual	Variation	CHANGE
186	Current Assets	32,200	89,703	105,797		91,037	(14,760)	58,837
187	Current Liabilities	24,523	48,773	48,223		34,643	(13,580)	10,120
188	WORKING CAPITAL	7,677	40,930	57,574		56,394	(1,180)	48,717
189
190	CURRENT RATIO	1.31	1.84	2.19		2.63	0.43	1.31
191
192	Long-term Liabilities	10,000	9,917	9,833		9,833	0	(167)
193	Capital	60,451	93,325	109,589		108,409	(1,180)	47,958
194
195	DEBT/EQUITY (GEARING)	0.17	0.11	0.09		0.09	(0.00)	(0.07)
196
197	Net Profit (A/Tax)	30,451	2,874	16,264	19,138	17,958	1,180	(12,493)
198	Capital at End	60,451	93,325	109,589	109,589	108,409	(1,180)	47,958
199	Return on Investment - pa	50.4%	37.0%	178.1%	104.8%	99.4%	-5.4%	49.0%

and now we move to the more advanced exercise “Jones Travel - Budget Model”.

the new features are:-

better layout for budgeting

front-end for assumptions about operating variables (which will be useful for sensitivity analysis)

funds statement included (short form and long form). You can choose one or the other approach for future modelling. The long form is preferred. Both should be completed for this exercise

take time to do this model properly

once you have prepared it copy Column B text to a new spreadsheet and try to develop the formulae etc by yourself. Have the previous model loaded as well so that you can switch from one to the other to help the learning process

this kind of model building is very fast and practical and can be used also in feasibility studies for major projects.

never lose sight of the value of modelling to produce forecasts of your ratios to compare with your financial objectives

A	B	C	D	E
2	JONES TRAVEL - BUDGET MODEL
3
4	PROFIT AND LOSS BUDGET ASSUMPTIONS
5
6		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
7		12 months	12 months	12 months
8		Actual	Budget	Budget
9	1. GROSS SALES GROWTH RATES
10	Air		10.0%	15.0%
11	Rail		5.0%	6.0%
12	Sea		8.0%	9.0%
13	Road		3.5%	4.0%
14	Accommodation		4.0%	5.0%
15	Tours		12.0%	13.0%
16
17	2. COMMISSION RATES
18	Air		7.5%	7.5%
19	Rail		15.0%	17.0%
20	Sea		17.0%	15.0%
21	Road		12.5%	12.5%
22	Accommodation		13.5%	13.5%
23	Tours		20.0%	20.0%
24
25	3. SOUVENIR SALES
26	Sales to groups (quantity)		1000	1500
27	Av. Price to groups		$15	$14
28	Retail sales (quantity)		2000	5000
29	Av. retail price		$20	$18
30
31	4. VARIABLE COSTS
32	Purchases		23.0%	21.0%
33	Direct Labour		12.0%	8.0%
34	Commission		10.0%	10.0%
35
36	5. STOCK ON HAND
37	% OF YEARS PURCHASES		20%	20%
38
39	6. ESCALATION OF OVERHEADS
40	Items marked *		8%	10%
41	PROFIT AND LOSS BUDGET
42		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
43		12 months	12 months	12 months
44		Actual	Budget	Budget
45	GROSS SALES VOLUME
46	Air	950,000	1,045,000	1,201,750
47	Rail	450,000	472,500	500,850
48	Sea	33,000	35,640	38,848
49	Road	10,000	10,350	10,764
50	Accommodation	5,000	5,200	5,460
51	Tours	47,000	52,640	59,483
52	Souvenirs	15,220	55,000	111,000
53	TOTAL GROSS SALES	1,510,220	1,676,330	1,928,155
54	Average Growth Rate pa		11.0%
55
56	GROSS PROFIT
57	1. COMMISSIONS
58	Air	69,350	78,375	90,131
59	% Comm. to sales	7.3%
60	Rail	67,500	70,875	85,145
61	% Comm. to sales	15.0%
62	Sea	5,610	6,059	5,827
63	% Comm. to sales	17.0%
64	Road	1,250	1,294	1,346
65	% Comm. to sales	12.5%
66	Accommodation	675	702	737
67	% Comm. to sales	13.5%
68	Tours	8,695	10,528	11,897
69	% Comm. to sales	18.5%
70
71	TOTAL COMMISSIONS	153,080	167,833	195,082
72	Average Commission %	10.2%	10%	10%
73
74
75		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
76	(continued)	12 months	12 months	12 months
77		Actual	Budget	Budget
78
79	2. SALE OF SOUVENIRS	15,220	55,000	111,000
80	LESS VARIABLE COSTS
81	MATERIAL
82	Stock at beginning	-	-	2,530
83	Purchases	4,500	12,650	23,310
84	Stock at End	-	(2,530)	(4,662)
85	COST- MATERIALS USED	4,500	10,120	21,178
86	Direct Labour	500	6,600	8,880
87	Commission Paid	200	5,500	11,100
88	TOTAL VARIABLE COSTS	5,200	22,220	41,158
89	CONTRIBUTION	10,020	32,780	69,842
90	Contribution %	66%	60%	63%
91
92	TOTAL GROSS PROFIT	163,100	200,613	264,924
93
94	(continued)	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
95		Actual	Budget	Budget
96	LESS OVERHEADS
97	Administrative Salaries		5,000	6,000
98	Advertising		5,000	7,000
99	Bank Charges *	1,563	1,688	1,857
100	Deprec.- Furniture & Fittings	555	555	555
101	Deprec.- Office Equipment	5,000	5,000	5,000
102	Directors Salaries		5,000	5,000
103	Electricity & Water *	560	605	665
104	General Expenses *	1,563	1,688	1,857
105	Insurance *	960	1,037	1,140
106	Interest on Bank Loan-10% pa		1,000	992
107	Leasing	4,300	4,300	4,300
108	Legal & Accounting *	2,000	2,160	2,376
109	Licence Fees *	1,500	1,620	1,750
110	Motor Vehicle Running Costs *	4,200	4,536	4,990
111	Printing & Stationery *	2,350	2,538	2,792
112	Rent	25,000	25,000	25,000
113	Repairs & Maintenance	1,632	3,000	4,000
114	Salaries	75,200	85,000	95,000
115	Staff Amenities	893	1,000	1,200
116	Subscriptions	1,650	1,700	1,800
117	Sundries		1,000	1,200
118	Telephone & Postage	3,200	4,000	4,500
119	Travel	523	2,000	3,000
120	TOTAL OVERHEADS	132,649	164,427	181,973
121	NET PROFIT (B/TAX)	30,451	36,186	82,951
122	Taxation @ 40%	12,180	14,474	33,180
123	NET PROFIT (A/TAX)	18,271	21,712	49,771
124	Dividend @ 20% (ie 80% retention)	-
125	NET PROFIT RETAINED	18,271	21,712	49,771
126	CASH FLOW BUDGET	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
127	INFLOW	Actual	Budget	Budget
128
129	Capital	50,000	30,000
130	Bank Loan	10,000		-
131	Receipts from Clients	24,023	20,000
132	Cash Sales (Commissions)	153,080	167,833	195,082
133	Debtors (Souvenirs)	9,539	5,681	55,000
134	TOTAL INFLOW	246,642	223,514	250,082
135	OUTFLOW
136	1. VARIABLE COSTS
137	Purchases (Creditors)	4,000	500	12,650
138	Direct Labour	500	6,600	8,880
139	Commission Paid	200	5,500	11,100
140	2. OVERHEADS
141	Administrative Salaries	0	5,000	6,000
142	Advertising	0	5000	7000
143	Bank Charges *	1563	1688.04	1856.844
144	Directors Salaries	-	5,000	5,000
145	Electricity & Water *	560	605	665
146	General Expenses *	1,563	1,688	1,857
147	Insurance *	960	1,200	1,200
148	Interest on Bank Loan-10% pa	-	1,000	992
149	Leasing	4,300	4,300	4,300
150	Legal & Accounting *	2,000	2,160	2,376
151	Licence Fees *	1,500	1,620	1,750
152	Motor Vehicle Running Costs *	4,200	4,536	4,990
153	Printing & Stationery *	2,350	2,538	2,792
154	Rent	25,000	25,000	25,000
155	Repairs & Maintenance	1,632	3,000	4,000
156	Salaries	75,200	85,000	95,000
157	Staff Amenities	893	1,000	1,200
158	Subscriptions	1,650	1,700	1,800
159	Sundries	-	1,000	1,200
160	Telephone & Postage	3,200	3,000	3,200
161	Travel	523	2,000	3,000
162
163	(continued)	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
164		Actual	Budget	Budget
165
166	3. ASSETS
167	Petty Cash Advance	100
168	Clients Trust Account	24,023	20,000	-
169	Furniture & Fittings	7,555
170	Office Equipment	29,000
171	Land & Buildings	31,774
172	4. LIABILITIES
173	Income Taxation		12,180	14,474
174	Dividend
175	Loan Principal (monthly, 10 years)		83	83
176	Return Capital
177	TOTAL OUTFLOW	224,246	202,899	222,365
178	NET CASH FLOW	22,396	20,615	27,717
179	PROGRESSIVE BALANCE	22,396	43,011	70,728
180	BALANCE SHEET BUDGET
181
182		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
183	CURRENT ASSETS	Actual	Budget	Budget
184	Cash at Bank	22,396	43,011	70,728
185	Client Trust Account	24,023	44,023	44,023
186	Petty Cash Advance	100	100	100
187	Prepayments - Insurance		163	223
188	Prepayments - Rent		-	-
189	Prepayments - Advertising		-	-
190	Stock (Souvenirs)	-	2,530	4,662
191	Debtors	5,681	55,000	111,000
192
193	TOTAL CURRENT ASSETS	52,200	144,827	230,735
194
195	FIXED ASSETS
196	Furniture & Fittings	7,555	7,555	7,555
197	Less Provision for Deprec.	(555)	(1,110)	(1,665)
198	Office Equipment	29,000	29,000	29,000
199	Less Provision for Deprec.	(5,000)	(10,000)	(15,000)
200	Land & Buildings	31,774	31,774	31,774
201	TOTAL FIXED ASSETS	62,774	57,219	51,664
202
203	TOTAL ASSETS	114,974	202,046	282,399
204
205	(continued)	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
206		Actual	Budget	Budget
207	CURRENT LIABILITIES
208	Creditors (Materials)	500	12,650	23,310
209	Client Ledger	24,023	44,023	44,023
210	Provision for Income Tax	12,180	14,474	33,180
211	Provision for Dividend
212	Accruals - Electricity & Water		-	-
213	Accruals - Telephone		1,000	2,300
214	Accruals - Salaries		-	-
215
216	TOTAL CURRENT LIABILITIES	36,703	72,147	102,813
217
218	LONG-TERM LIABS
219	Long-term Loan	10,000	9,917	9,833
220
221	TOTAL LONG TERM LIABILITIES	10,000	9,917	9,833
222
223	CAPITAL
224	Capital at Beginning	50,000	80,000	80,000
225	Net Profit Retained	18,271	39,982	89,753
226	TOTAL CAPITAL	68,271	119,982	169,753
227
228	TOTAL LIABILITIES	114,974	202,046	282,399
229	Proof	-	-	(0)
230
231	BUDGETED RATIOS	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
232		Actual	Budget	Budget
233
234	Current Assets	52,200	144,827	230,735
235	Current Liabilities	36,703	72,147	102,813
236	WORKING CAPITAL	15,497	72,680	127,922
237
238	CURRENT RATIO	1.42	2.01	2.24
239
240	Long-term Liabilities	10,000	9,917	9,833
241	Capital	68,271	119,982	169,753
242
243	DEBT/EQUITY (GEARING)	0.15	0.08	0.06
244
245	Net Profit (A/Tax)	18,271	21,712	49,771
246	Capital at End	68,271	119,982	169,753
247	Return on Investment - pa	26.8%	18.1%	29.3%
248
249	CHANGES IN BALANCE SHEETS		YEAR 1	YEAR 1
250			95/96	96/97
251	CURRENT ASSETS
252	Cash at Bank		20,615	27,717
253	Client Trust Account		20,000	-
254	Petty Cash Advance		-	-
255	Prepayments - Insurance		163	60
256	Prepayments - Rent		-	-
257	Prepayments - Advertising		-	-
258	Stock (Souvenirs)		2,530	2,132
259	Debtors		49,319	56,000
260	Sub-total		92,627	85,908
261	FIXED ASSETS
262	Furniture & Fittings		-	-
263	Less Provision for Deprec.		(555)	(555)
264	Office Equipment		-	-
265	Less Provision for Deprec.		(5,000)	(5,000)
266	Land & Buildings		-	-
267	Sub-total		(5,555)	(5,555)
268	CHANGES IN TOTAL ASSETS		87,072	80,353
269
270	CURRENT LIABILITIES
271	Creditors (Materials)		12,150	10,660
272	Client Ledger		20,000	-
273	Provision for Income Tax		2,294	18,706
274	Provision for Dividend		-	-
275	Accruals - Electricity & Water		-	-
276	Accruals - Telephone		1,000	1,300
277	Accruals - Salaries		-	-
278	Sub-total		35,444	30,666
279	LONG TERM LIABILITIES
280	Long-term Loan		(83)	(83)
281	Sub-total		(83)	(83)
282	OWNERS EQUITY
283	Capital at Beginning		30,000	-
284	Net Profit Retained		21,712	49,771
285	Sub-total		51,712	49,771
286	CHANGES IN TOTAL LIABILITIES		87,072	80,353
287	DIFFERENCE		(0)	(0)
288	DRAFT FUNDS STATEMENT BUDGET		Y/E 30/6/96	Y/E 30/6/97
289	(Uses negative, sources positive)		Budget	Budget
290	Net Profit (After tax & div)		21,712	49,771
291	Add back depreciation		5,555	5,555
292	Funds from Operations		27,267	55,326
293	Cash at Bank		(20,615)	(27,717)
294	Client Trust Account		(20,000)	-
295	Petty Cash Advance		-	-
296	Prepayments - Insurance		(163)	(60)
297	Prepayments - Rent		-	-
298	Prepayments - Advertising		-	-
299	Stock (Souvenirs)		(2,530)	(2,132)
300	Debtors		(49,319)	(56,000)
301	Furniture & Fittings		0	0
302	Office Equipment		0	0
303	Land & Buildings		0	0
304	Creditors (Materials)		12,150	10,660
305	Client Ledger		20,000	-
306	Provision for Income Tax		2,294	18,706
307	Provision for Dividend		-	-
308	Accruals - Electricity & Water		-	-
309	Accruals - Telephone		1,000	1,300
310	Accruals - Salaries		-	-
311	Long-term Loan		(83)	(83)
312	Capital at Beginning		30,000	-
313	DIFF		0	0
314	TOTAL SOURCES		92,710	85,992
315	TOTAL USES		92,710	85,992
316
317	DO NOT PRINT INFO BELOW		Y/E 30/6/96	Y/E 30/6/97
318	Funds from Operations		1	1
319	Cash at Bank		-	-
320	Client Trust Account		-	-
321	Petty Cash Advance		-	-
322	Prepayments - Insurance		-	-
323	Prepayments - Rent		-	-
324	Prepayments - Advertising		-	-
325	Stock (Souvenirs)		-	-
326	Debtors		-	-
327	Furniture & Fittings		-	-
328	Office Equipment		-	-
329	Land & Buildings		-	-
330	Creditors (Materials)		1	1
331	Client Ledger		1	-
332	Provision for Income Tax		1	1
333	Provision for Dividend		-	-
334	Accruals - Electricity & Water		-	-
335	Accruals - Telephone		1	1
336	Accruals - Salaries		-	-
337	Long-term Loan		-	-
338	Capital at Beginning		1	-
339	FUNDS STATEMENT BUDGET		Y/E 30/6/96	Y/E 30/6/97
340
341	SOURCES OF FUNDS
342	Funds from Operations		27,267	55,326
343	Cash at Bank		-	-
344	Client Trust Account		-	-
345	Petty Cash Advance		-	-
346	Prepayments - Insurance		-	-
347	Prepayments - Rent		-	-
348	Prepayments - Advertising		-	-
349	Stock (Souvenirs)		-	-
350	Debtors		-	-
351	Furniture & Fittings		-	-
352	Office Equipment		-	-
353	Land & Buildings		-	-
354	Creditors (Materials)		12,150	10,660
355	Client Ledger		20,000	-
356	Provision for Income Tax		2,294	18,706
357	Provision for Dividend		-	-
358	Accruals - Electricity & Water		-	-
359	Accruals - Telephone		1,000	1,300
360	Accruals - Salaries		-	-
361	Long-term Loan		-	-
362	Capital at Beginning		30,000	-
363
364	TOTAL SOURCES OF FUNDS		92,710	85,992
365
366	(continued)		Y/E 30/6/96	Y/E 30/6/97
367
368	APPLICATION OF FUNDS
369	Funds from Operations		-	-
370	Cash at Bank		20,615	27,717
371	Client Trust Account		20,000	-
372	Petty Cash Advance		-	-
373	Prepayments - Insurance		163	60
374	Prepayments - Rent		-	-
375	Prepayments - Advertising		-	-
376	Stock (Souvenirs)		2,530	2,132
377	Debtors		49,319	56,000
378	Furniture & Fittings		-	-
379	Office Equipment		-	-
380	Land & Buildings		-	-
381	Creditors (Materials)		-	-
382	Client Ledger		-	-
383	Provision for Income Tax		-	-
384	Provision for Dividend		-	-
385	Accruals - Electricity & Water		-	-
386	Accruals - Telephone		-	-
387	Accruals - Salaries		-	-
388	Long-term Loan		83	83
389	Capital at Beginning		-	-
390
391	TOTAL USE OF FUNDS		92,710	85,992

A	B	C	D	E
2	JONES TRAVEL -	BUDGET MODEL
3
4	PROFIT AND LOSS BUDGET	= ASSUMPTIONS
5
6		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
7		12 months	12 months	12 months
8		Actual	Budget	Budget
9	1. GROSS SALES GROWTH RATES
10	Air		0.1	0.15
11	Rail		0.05	0.06
12	Sea		0.08	0.09
13	Road		0.035	0.04
14	Accommodation		0.04	0.05
15	Tours		0.12	0.13
16
17	2. COMMISSION RATES
18	Air		0.075	0.075
19	Rail		0.15	0.17
20	Sea		0.17	0.15
21	Road		0.125	0.125
22	Accommodation		0.135	0.135
23	Tours		0.2	0.2
24
25	3. SOUVENIR SALES
26	Sales to groups (quantity)		1000	1500
27	Av. Price to groups		15	14
28	Retail sales (quantity)		2000	5000
29	Av. retail price		20	18
30
31	4. VARIABLE COSTS
32	Purchases		0.23	0.21
33	Direct Labour		0.12	0.08
34	Commission		0.1	0.1
35
36	5. STOCK ON HAND
37	% OF YEARS PURCHASES		0.2	0.2
38
39	6. ESCALATION OF OVERHEADS
40	Items marked *		0.08	0.1
41	PROFIT AND LOSS BUDGET
42		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
43		12 months	12 months	12 months
44		Actual	Budget	Budget
45	GROSS SALES VOLUME
46	Air	950000	=+C46+(C46*D10)	=+D46+(D46*E10)
47	Rail	450000	=+C47+(C47*D11)	=+D47+(D47*E11)
48	Sea	33000	=+C48+(C48*D12)	=+D48+(D48*E12)
49	Road	10000	=+C49+(C49*D13)	=+D49+(D49*E13)
50	Accommodation	5000	=+C50+(C50*D14)	=+D50+(D50*E14)
51	Tours	47000	=+C51+(C51*D15)	=+D51+(D51*E15)
52	Souvenirs	15220	=(+D26D27)+(D28D29)	=(+E26E27)+(E28E29)
53	TOTAL GROSS SALES	=SUM(C46:C52)	=SUM(D46:D52)	=SUM(E46:E52)
54	Average Growth Rate pa		=(+D53/C53)-1
55
56	GROSS PROFIT
57	1. COMMISSIONS
58	Air	69350	=+D46*D18	=+E46*E18
59	% Comm. to sales	=+C58/C46
60	Rail	67500	=+D47*D19	=+E47*E19
61	% Comm. to sales	=+C60/C47
62	Sea	5610	=+D48*D20	=+E48*E20
63	% Comm. to sales	=+C62/C48
64	Road	1250	=+D49*D21	=+E49*E21
65	% Comm. to sales	=+C64/C49
66	Accommodation	675	=+D50*D22	=+E50*E22
67	% Comm. to sales	=+C66/C50
68	Tours	8695	=+D51*D23	=+E51*E23
69	% Comm. to sales	=+C68/C51
70
71	TOTAL COMMISSIONS	=+C68+C66+C64+C62+C60+C58	=+D68+D66+D64+D62+D60+D58	=+E68+E66+E64+E62+E60+E58
72	Average Commission %	=+C71/(C53-C52)	=+D71/D53	=+E71/E53
73
74
75		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
76	(continued)	12 months	12 months	12 months
77		Actual	Budget	Budget
78
79	2. SALE OF SOUVENIRS	15220	=+D52	=+E52
80	LESS VARIABLE COSTS
81	MATERIAL
82	Stock at beginning	0	0	=D84*-1
83	Purchases	4500	=+D79*D32	=+E79*E32
84	Stock at End	0	=-D83*D37	=-E83*E37
85	COST- MATERIALS USED	=C82+C83+C84	=D82+D83+D84	=E82+E83+E84
86	Direct Labour	500	=+D79*D33	=+E79*E33
87	Commission Paid	200	=+D79*D34	=+E79*E34
88	TOTAL VARIABLE COSTS	=SUM(C85:C87)	=SUM(D85:D87)	=SUM(E85:E87)
89	CONTRIBUTION	=+C79-C88	=D79-D88	=E79-E88
90	Contribution %	=+C89/C79	=+D89/D79	=+E89/E79
91
92	TOTAL GROSS PROFIT	=+C89+C71	=+D89+D71	=+E89+E71
93
94	(continued)	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
95		Actual	Budget	Budget
96	LESS OVERHEADS
97	Administrative Salaries		5000	6000
98	Advertising		5000	7000
99	Bank Charges *	1563	=+C99+(C99*D$40)	=+D99+(D99*E$40)
100	Deprec.- Furniture & Fittings	555	=+C100	=+D100
101	Deprec.- Office Equipment	5000	=+C101	=+D101
102	Directors Salaries		5000	5000
103	Electricity & Water *	560	=+C103+(C103*D$40)	=+D103+(D103*E$40)
104	General Expenses *	1563	=+C104+(C104*D$40)	=+D104+(D104*E$40)
105	Insurance *	960	=+C105+(C105*D$40)	=+D105+(D105*E$40)
106	Interest on Bank Loan-10% pa		=+C219*0.1	=+D219*0.1
107	Leasing	4300	=+C107	=+D107
108	Legal & Accounting *	2000	=+C108+(C108*D$40)	=+D108+(D108*E$40)
109	Licence Fees *	1500	=+C109+(C109*D$40)	=+D109+(D109*$D$40)
110	Motor Vehicle Running Costs *	4200	=+C110+(C110*D$40)	=+D110+(D110*E$40)
111	Printing & Stationery *	2350	=+C111+(C111*D$40)	=+D111+(D111*E$40)
112	Rent	25000	=+C112	=+D112
113	Repairs & Maintenance	1632	3000	4000
114	Salaries	75200	85000	95000
115	Staff Amenities	893	1000	1200
116	Subscriptions	1650	1700	1800
117	Sundries		1000	1200
118	Telephone & Postage	3200	4000	4500
119	Travel	523	2000	3000
120	TOTAL OVERHEADS	=SUM(C97:C119)	=SUM(D97:D119)	=SUM(E97:E119)
121	NET PROFIT (B/TAX)	=+C92-C120	=+D92-D120	=+E92-E120
122	Taxation @ 40%	=+C121*0.4	=+D121*0.4	=+E121*0.4
123	NET PROFIT (A/TAX)	=+C121-C122	=+D121-D122	=+E121-E122
124	Dividend @ 20% (ie 80% retention)	0
125	NET PROFIT RETAINED	=+C123-C124	=+D123-D124	=+E123-E124
126	CASH FLOW BUDGET	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
127	INFLOW	Actual	Budget	Budget
128
129	Capital	50000	30000
130	Bank Loan	10000		0
131	Receipts from Clients	24023	20000
132	Cash Sales (Commissions)	=+C71	=+D71	=+E71
133	Debtors (Souvenirs)	9539	=+C191	=+D191
134	TOTAL INFLOW	=SUM(C129:C133)	=SUM(D129:D133)	=SUM(E129:E133)
135	OUTFLOW
136	1. VARIABLE COSTS
137	Purchases (Creditors)	4000	=+C208	=+D208
138	=+B86	=+C86	=+D86	=+E86
139	=+B87	=+C87	=+D87	=+E87
140	2. OVERHEADS
141	=+B97	=+C97	=+D97	=+E97
142	=+B98	=+C98	=+D98	=+E98
143	=+B99	=+C99	=+D99	=+E99
144	=+B102	=+C102	=+D102	=+E102
145	=+B103	=+C103	=+D103	=+E103
146	=+B104	=+C104	=+D104	=+E104
147	=+B105	=+C105	1200	1200
148	=+B106	=+C106	=+D106	=+E106
149	=+B107	=+C107	=+D107	=+E107
150	=+B108	=+C108	=+D108	=+E108
151	=+B109	=+C109	=+D109	=+E109
152	=+B110	=+C110	=+D110	=+E110
153	=+B111	=+C111	=+D111	=+E111
154	=+B112	=+C112	=+D112	=+E112
155	=+B113	=+C113	=+D113	=+E113
156	=+B114	=+C114	=+D114	=+E114
157	=+B115	=+C115	=+D115	=+E115
158	=+B116	=+C116	=+D116	=+E116
159	=+B117	=+C117	=+D117	=+E117
160	=+B118	=+C118	3000	3200
161	=+B119	=+C119	=+D119	=+E119
162
163
164
165
166	3. ASSETS
167	Petty Cash Advance	100
168	Clients Trust Account	=+C131	=+D131	=+E131
169	Furniture & Fittings	7555
170	Office Equipment	29000
171	Land & Buildings	31774
172	4. LIABILITIES
173	Income Taxation		=+C122	=+D122
174	Dividend
175	Loan Principal (monthly, 10 years)		=+C130/120	=+D175
176	Return Capital
177	TOTAL OUTFLOW	=SUM(C137:C175)	=SUM(D137:D175)	=SUM(E137:E175)
178	NET CASH FLOW	=+C134-C177	=+D134-D177	=+E134-E177
179	PROGRESSIVE BALANCE	=+C178	=+C179+D178	=+D179+E178
180	BALANCE SHEET BUDGET
181
182		Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
183	CURRENT ASSETS	Actual	Budget	Budget
184	Cash at Bank	=+C179	=+D179	=+E179
185	Client Trust Account	=+C168	=+C185+D131	=+D185+E131
186	Petty Cash Advance	=+C167	=+C186	=+D186
187	Prepayments - Insurance		=+C187+D147-D105	=+D187+E147-E105
188	Prepayments - Rent		=+C188+D154-D112	=+D188+E154-E112
189	Prepayments - Advertising		=+C189+D142-D98	=+D189+E142-E98
190	Stock (Souvenirs)	=-C84	=-D84	=-E84
191	Debtors	=+C79-C133	=+C191+D79-D133	=+D191+E79-E133
192
193	TOTAL CURRENT ASSETS	=SUM(C184:C191)	=SUM(D184:D191)	=SUM(E184:E191)
194
195	FIXED ASSETS
196	Furniture & Fittings	=+C169	=+C196+D169	=+D196+E169
197	Less Provision for Deprec.	=-C100	=+C197-D100	=+D197-E100
198	Office Equipment	=+C170	=+C198+D170	=+D198+E170
199	Less Provision for Deprec.	=-C101	=+C199-D101	=+D199-E101
200	Land & Buildings	=+C171	=+C200+D171	=+D200+E171
201	TOTAL FIXED ASSETS	=SUM(C196:C200)	=SUM(D196:D200)	=SUM(E196:E200)
202
203	TOTAL ASSETS	=+C201+C193	=+D201+D193	=+E201+E193
204
205	(continued)	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
206		Actual	Budget	Budget
207	CURRENT LIABILITIES
208	Creditors (Materials)	=+C83-C137	=+D83-D137+C208	=+E83-E137+D208
209	Client Ledger	=+C131	=+D131+C209	=+E131+D209
210	Provision for Income Tax	=+C122	=+C210+D122-D173	=+D210+E122-E173
211	Provision for Dividend
212	Accruals - Electricity & Water		=+C212+D103-D145	=+D212+E103-E145
213	Accruals - Telephone		=+C213+D118-D160	=+D213+E118-E160
214	Accruals - Salaries		=+C214+D114-D156	=+D214+E114-E156
215
216	TOTAL CURRENT LIABILITIES	=SUM(C208:C214)	=SUM(D208:D214)	=SUM(E208:E214)
217
218	LONG-TERM LIABS
219	Long-term Loan	=+C130	=+C219+D130-D175	=+D219+E130-E175
220
221	TOTAL LONG TERM LIABILITIES	=+C219	=+D219	=+E219
222
223	CAPITAL
224	Capital at Beginning	=+C129	=+C224+D129-D176	=+D224+E129-E176
225	Net Profit Retained	=+C125	=+C225+D125	=+D225+E125
226	TOTAL CAPITAL	=+C224+C225	=+D224+D225	=+E224+E225
227
228	TOTAL LIABILITIES	=+C226+C221+C216	=+D226+D221+D216	=+E226+E221+E216
229	Proof	=+C203-C228	=+D203-D228	=+E203-E228
230
231	BUDGETED RATIOS	Y/E 30/6/95	Y/E 30/6/96	Y/E 30/6/97
232		Actual	Budget	Budget
233
234	Current Assets	=+C193	=+D193	=+E193
235	Current Liabilities	=+C216	=+D216	=+E216
236	WORKING CAPITAL	=+C234-C235	=+D234-D235	=+E234-E235
237
238	CURRENT RATIO	=+C234/C235	=+D234/D235	=+E234/E235
239
240	Long-term Liabilities	=+C221	=+D221	=+E221
241	Capital	=+C226	=+D226	=+E226
242
243	DEBT/EQUITY (GEARING)	=+C240/C241	=+D240/D241	=+E240/E241
244
245	Net Profit (A/Tax)	=+C123	=+D123	=+E123
246	Capital at End	=+C226	=+D226	=+E226
247	Return on Investment - pa	=+C245/C246	=+D245/D246	=+E245/E246
248
249	CHANGES IN BALANCE SHEETS		YEAR 1	YEAR 1
250			95/96	96/97
251	CURRENT ASSETS
252	=+B184		=-C184+D184	=-D184+E184
253	=+B185		=-C185+D185	=-D185+E185
254	=+B186		=-C186+D186	=-D186+E186
255	=+B187		=-C187+D187	=-D187+E187
256	=+B188		=-C188+D188	=-D188+E188
257	=+B189		=-C189+D189	=-D189+E189
258	=+B190		=-C190+D190	=-D190+E190
259	=+B191		=-C191+D191	=-D191+E191
260	Sub-total		=SUM(D252:D259)	=SUM(E252:E259)
261	FIXED ASSETS
262	=+B196		=-C196+D196	=-D196+E196
263	=+B197		=(+D197-C197)	=(+E197-D197)
264	=+B198		=-C198+D198	=-D198+E198
265	=+B199		=(+D199-C199)	=(+E199-D199)
266	=+B200		=-C200+D200	=-D200+E200
267	Sub-total		=SUM(D262:D266)	=SUM(E262:E266)
268	CHANGES IN TOTAL ASSETS		=+D267+D260	=+E267+E260
269
270	CURRENT LIABILITIES
271	=+B208		=-C208+D208	=-D208+E208
272	=+B209		=-C209+D209	=-D209+E209
273	=+B210		=-C210+D210	=-D210+E210
274	=+B211		=-C211+D211	=-D211+E211
275	=+B212		=-C212+D212	=-D212+E212
276	=+B213		=-C213+D213	=-D213+E213
277	=+B214		=-C214+D214	=-D214+E214
278	Sub-total		=SUM(D271:D277)	=SUM(E271:E277)
279	LONG TERM LIABILITIES
280	=+B219		=-C219+D219	=-D219+E219
281	Sub-total		=+D280	=+E280
282	OWNERS EQUITY
283	=+B224		=-C224+D224	=-D224+E224
284	=+B225		=-C225+D225	=-D225+E225
285	Sub-total		=+D283+D284	=+E283+E284
286	CHANGES IN TOTAL LIABILITIES		=+D285+D281+D278	=+E285+E281+E278
287	DIFFERENCE		=+D268-D286	=+E268-E286
288	DRAFT FUNDS STATEMENT BUDGET		Y/E 30/6/96	Y/E 30/6/97
289	(Uses negative, sources positive)		Budget	Budget
290	Net Profit (After tax & div)		=+D125	=+E125
291	Add back depreciation		=+D100+D101	=+E100+E101
292	Funds from Operations		=+D290+D291	=+E290+E291
293	=+B252		=+D252*-1	=+E252*-1
294	=+B253		=+D253*-1	=+E253*-1
295	=+B254		=+D254*-1	=+E254*-1
296	=+B255		=+D255*-1	=+E255*-1
297	=+B256		=+D256*-1	=+E256*-1
298	=+B257		=+D257*-1	=+E257*-1
299	=+B258		=+D258*-1	=+E258*-1
300	=+B259		=+D259*-1	=+E259*-1
301	=+B262		=+D262*-1	=+E262*-1
302	=+B264		=+D264*-1	=+E264*-1
303	=+B266		=+D266*-1	=+E266*-1
304	=+B271		=+D271	=+E271
305	=+B272		=+D272	=+E272
306	=+B273		=+D273	=+E273
307	=+B274		=+D274	=+E274
308	=+B275		=+D275	=+E275
309	=+B276		=+D276	=+E276
310	=+B277		=+D277	=+E277
311	=+B280		=+D280	=+E280
312	=+B283		=+D283	=+E283
313	DIFF		=SUM(D292:D312)	=SUM(E292:E312)
314	TOTAL SOURCES		=+D292D318+(D293D319)+(D294D320)+(D295D321)+(D296D322)+(D297D323)+(D298D324)+(D299D325)+(D300D326)+(D301D327)+(D302D328)+(D303D329)+(D304D330)+(D305D331)+(D306D332)+(D307D333)+(D308* D334)+(D309D335)+(D310D336 )+(D311D337)+(D312D338)	=+E292E318+(E293E319)+(E294E320)+(E295E321)+(E296E322)+(E297E323)+(E298E324)+(E299E325)+(E300E326)+(E301E327)+(E302E328)+(E303E329)+(E304E330)+(E305E331)+(E306E332)+(E307E333)+(E308* E334)+(E309E335)+(E310E336 )+(E311E337)+(E312E338)
315	TOTAL USES		=+D314	=+E314
316
317	DO NOT PRINT INFO BELOW		Y/E 30/6/96	Y/E 30/6/97
318	=+B292		=+D292>0	=+E292>0
319	=+B293		=+D293>0	=+E293>0
320	=+B294		=+D294>0	=+E294>0
321	=+B295		=+D295>0	=+E295>0
322	=+B296		=+D296>0	=+E296>0
323	=+B297		=+D297>0	=+E297>0
324	=+B298		=+D298>0	=+E298>0
325	=+B299		=+D299>0	=+E299>0
326	=+B300		=+D300>0	=+E300>0
327	=+B301		=+D301>0	=+E301>0
328	=+B302		=+D302>0	=+E302>0
329	=+B303		=+D303>0	=+E303>0
330	=+B304		=+D304>0	=+E304>0
331	=+B305		=+D305>0	=+E305>0
332	=+B306		=+D306>0	=+E306>0
333	=+B307		=+D307>0	=+E307>0
334	=+B308		=+D308>0	=+E308>0
335	=+B309		=+D309>0	=+E309>0
336	=+B310		=+D310>0	=+E310>0
337	=+B311		=+D311>0	=+E311>0
338	=+B312		=+D312>0	=+E312>0
339	FUNDS STATEMENT BUDGET		Y/E 30/6/96	Y/E 30/6/97
340
341	SOURCES OF FUNDS
342	=+B292		=IF(D292>0,D292,0)	=IF(E292>0,E292,0)
343	=+B293		=IF(D293>0,D293,0)	=IF(E293>0,E293,0)
344	=+B294		=IF(D294>0,D294,0)	=IF(E294>0,E294,0)
345	=+B295		=IF(D295>0,D295,0)	=IF(E295>0,E295,0)
346	=+B296		=IF(D296>0,D296,0)	=IF(E296>0,E296,0)
347	=+B297		=IF(D297>0,D297,0)	=IF(E297>0,E297,0)
348	=+B298		=IF(D298>0,D298,0)	=IF(E298>0,E298,0)
349	=+B299		=IF(D299>0,D299,0)	=IF(E299>0,E299,0)
350	=+B300		=IF(D300>0,D300,0)	=IF(E300>0,E300,0)
351	=+B301		=IF(D301>0,D301,0)	=IF(E301>0,E301,0)
352	=+B302		=IF(D302>0,D302,0)	=IF(E302>0,E302,0)
353	=+B303		=IF(D303>0,D303,0)	=IF(E303>0,E303,0)
354	=+B304		=IF(D304>0,D304,0)	=IF(E304>0,E304,0)
355	=+B305		=IF(D305>0,D305,0)	=IF(E305>0,E305,0)
356	=+B306		=IF(D306>0,D306,0)	=IF(E306>0,E306,0)
357	=+B307		=IF(D307>0,D307,0)	=IF(E307>0,E307,0)
358	=+B308		=IF(D308>0,D308,0)	=IF(E308>0,E308,0)
359	=+B309		=IF(D309>0,D309,0)	=IF(E309>0,E309,0)
360	=+B310		=IF(D310>0,D310,0)	=IF(E310>0,E310,0)
361	=+B311		=IF(D311>0,D311,0)	=IF(E311>0,E311,0)
362	=+B312		=IF(D312>0,D312,0)	=IF(E312>0,E312,0)
363
364	TOTAL SOURCES OF FUNDS		=SUM(D342:D362)	=SUM(E342:E362)
365
366	(continued)		Y/E 30/6/96	Y/E 30/6/97
367
368	APPLICATION OF FUNDS
369	=+B292		=IF(D292<0,D292,0)*-1	=IF(E292<0,E292,0)*-1
370	=+B293		=IF(D293<0,D293,0)*-1	=IF(E293<0,E293,0)*-1
371	=+B294		=IF(D294<0,D294,0)*-1	=IF(E294<0,E294,0)*-1
372	=+B295		=IF(D295<0,D295,0)*-1	=IF(E295<0,E295,0)*-1
373	=+B296		=IF(D296<0,D296,0)*-1	=IF(E296<0,E296,0)*-1
374	=+B297		=IF(D297<0,D297,0)*-1	=IF(E297<0,E297,0)*-1
375	=+B298		=IF(D298<0,D298,0)*-1	=IF(E298<0,E298,0)*-1
376	=+B299		=IF(D299<0,D299,0)*-1	=IF(E299<0,E299,0)*-1
377	=+B300		=IF(D300<0,D300,0)*-1	=IF(E300<0,E300,0)*-1
378	=+B301		=IF(D301<0,D301,0)*-1	=IF(E301<0,E301,0)*-1
379	=+B302		=IF(D302<0,D302,0)*-1	=IF(E302<0,E302,0)*-1
380	=+B303		=IF(D303<0,D303,0)*-1	=IF(E303<0,E303,0)*-1
381	=+B304		=IF(D304<0,D304,0)*-1	=IF(E304<0,E304,0)*-1
382	=+B305		=IF(D305<0,D305,0)*-1	=IF(E305<0,E305,0)*-1
383	=+B306		=IF(D306<0,D306,0)*-1	=IF(E306<0,E306,0)*-1
384	=+B307		=IF(D307<0,D307,0)*-1	=IF(E307<0,E307,0)*-1
385	=+B308		=IF(D308<0,D308,0)*-1	=IF(E308<0,E308,0)*-1
386	=+B309		=IF(D309<0,D309,0)*-1	=IF(E309<0,E309,0)*-1
387	=+B310		=IF(D310<0,D310,0)*-1	=IF(E310<0,E310,0)*-1
388	=+B311		=IF(D311<0,D311,0)*-1	=IF(E311<0,E311,0)*-1
389	=+B312		=IF(D312<0,D312,0)*-1	=IF(E312<0,E312,0)*-1
390
391	TOTAL USE OF FUNDS		=SUM(D369:D389)	=SUM(E369:E389)

Sensitivity Analysis

copy the “Jones Travel - Budget Model” file to a new file-name

conduct relevant sensitivity analyses by manipulating model variables in order to reflect the following:

break-even level (where Net Profit equals zero)

a desired rate of return on investment

alternative pessimistic, realistic and optimistic outcomes.

Contingency planning

set up a new file for the “realistic” budget with one money column only for the budget year ending 30/6/96

set up new columns for actual and variation

enter the “pessimistic” figures in as actuals (via copy & paste) and calculate variations

discuss an action plan to improve liquidity, profitability and security and demonstrate these possible actions in the spread-sheet

Copyright © Bill Wright 1994

UNIT 15 SPREAD-SHEET BUDGETING – PART 2

C83

Copyright © 2000 Genesis Management Services Pty Ltd Last modified: July 18, 2006

Copyright © 2000 Genesis Management Services Pty Ltd
Last modified: July 18, 2006